﻿using FineUIPro;
using KPI_SYSTEM.Code;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace KPI_SYSTEM.SalaryPage
{
    public partial class SalaryTemplateList : PageBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadBaseData();
                BindData();
            }
            PublicMethod.GetApprovalButton("SalaryTemplateList.aspx", Toolbar2, Grid1);
        }

        /// <summary>
        /// 加载页面必备数据
        /// </summary>
        private void LoadBaseData()
        {
            DataTable tableSalaryItem = SqlHelper.ExecuteDataset(CommandType.Text, @"SELECT REC_ID,(SALARY_ITEM_NAME+'(编号：'+SALARY_ITEM_CODE+')') AS ITEM_NAME FROM HOS_SALARY_ITEM WHERE SALARY_ITEM_TYPE=1 ORDER BY SALARY_ITEM_SEQ").Tables[0];
            chlFormSALARY_ITEMS.DataTextField = "ITEM_NAME";
            chlFormSALARY_ITEMS.DataValueField = "REC_ID";
            chlFormSALARY_ITEMS.DataSource = tableSalaryItem;
            chlFormSALARY_ITEMS.DataBind();


            DataTable tableDeductItem = SqlHelper.ExecuteDataset(CommandType.Text, @"SELECT REC_ID,(SALARY_ITEM_NAME+'(编号：'+SALARY_ITEM_CODE+')') AS ITEM_NAME FROM HOS_SALARY_ITEM WHERE SALARY_ITEM_TYPE=0 ORDER BY SALARY_ITEM_SEQ").Tables[0];
            chlFormDEDUCT_ITEMS.DataTextField = "ITEM_NAME";
            chlFormDEDUCT_ITEMS.DataValueField = "REC_ID";
            chlFormDEDUCT_ITEMS.DataSource = tableDeductItem;
            chlFormDEDUCT_ITEMS.DataBind();


            DataTable tableTotalSalaryItem = SqlHelper.ExecuteDataset(CommandType.Text, @"SELECT REC_ID,(TOTAL_ITEM_NAME+'(编号：'+TOTAL_ITEM_CODE+')') AS ITEM_NAME FROM HOS_SALARY_TOTAL WHERE TOTAL_ITEM_TYPE=1 ORDER BY TOTAL_ITEM_SEQ").Tables[0];
            chlFormTOTAL_SALARY_ITEMS.DataTextField = "ITEM_NAME";
            chlFormTOTAL_SALARY_ITEMS.DataValueField = "REC_ID";
            chlFormTOTAL_SALARY_ITEMS.DataSource = tableTotalSalaryItem;
            chlFormTOTAL_SALARY_ITEMS.DataBind();

            DataTable tableTotalDeductItem = SqlHelper.ExecuteDataset(CommandType.Text, @"SELECT REC_ID,(TOTAL_ITEM_NAME+'(编号：'+TOTAL_ITEM_CODE+')') AS ITEM_NAME FROM HOS_SALARY_TOTAL WHERE TOTAL_ITEM_TYPE=0 ORDER BY TOTAL_ITEM_SEQ").Tables[0];
            chlFormTOTAL_DEDUCT_ITEMS.DataTextField = "ITEM_NAME";
            chlFormTOTAL_DEDUCT_ITEMS.DataValueField = "REC_ID";
            chlFormTOTAL_DEDUCT_ITEMS.DataSource = tableTotalDeductItem;
            chlFormTOTAL_DEDUCT_ITEMS.DataBind();

        }
        /// <summary>
        /// 加载页面列表数据
        /// </summary>
        private void BindData()
        {
            StringBuilder sqlWhere = new StringBuilder();
            String mainDataSql = @"SELECT REC_ID,TEMPLATE_NAME,TEMPLATE_DATA,TEMPLATE_REMARK,TEMPLATE_STATUS,TEMPLATE_ITEM,TEMPLATE_TOTAL,
                                    CASE WHEN TEMPLATE_STATUS=0 THEN '未上传' WHEN TEMPLATE_STATUS=1 THEN '已上传' END AS TEMPLATE_STATUS_NAME
                                    FROM HOS_SALARY_TEMPLATE WHERE 1=1 {0}";
            if (tbxNameSerach.Text.Trim() != "")
            {
                sqlWhere.Append(" AND TEMPLATE_NAME LIKE '%" + tbxNameSerach.Text.Trim() + "%'");
            }
            if (tbxMonthSearch.Text.Trim() != "")
            {
                sqlWhere.Append(" AND TEMPLATE_DATA LIKE '%" + tbxMonthSearch.Text.Trim() + "%'");
            }
            mainDataSql = string.Format(mainDataSql, sqlWhere.ToString());
            DataTable mainDataTable = SqlHelper.ExecuteDataset(CommandType.Text, mainDataSql).Tables[0];
            Grid1.DataSource = mainDataTable;
            Grid1.DataBind();
        }
        private string GetExcel()
        {
            string fileUrl = "";
            try
            {
                //全名  
                string excelFile = this.fileUploadExcel.PostedFile.FileName;
                //获取文件名（不包括扩展名）  
                string fileName = Path.GetFileNameWithoutExtension(fileUploadExcel.PostedFile.FileName);
                //扩展名  
                string extentionName = excelFile.Substring(excelFile.LastIndexOf(".") + 1);
                if (fileName == "" || fileName == null)
                {
                    Response.Write("<script>alert('请先选择Excel文件！')</script>");
                    return null;
                }
                if (extentionName != "xls" && extentionName != "xlsx")
                {
                    Response.Write("<script>alert('您上传的不是Excel文件！')</script>");
                    return null;
                }
                //浏览器安全性限制 无法直接获取客户端文件的真实路径，将文件上传到服务器端 然后获取文件源路径  
                string dateTime = DateTime.Now.Date.ToString("yyyyMMdd");
                string time = DateTime.Now.ToShortTimeString().Replace(":", "");
                string newFileName = dateTime + time + DateTime.Now.Millisecond.ToString() + "." + extentionName;
                //自己创建的文件夹 位置随意 合理即可  
                fileUrl = Server.MapPath("..\\SalaryExcel") + "\\" + newFileName;
                this.fileUploadExcel.PostedFile.SaveAs(fileUrl);
                //Response.Write("<script>alert('已经上传到服务器文件夹')</script>");
                return fileUrl;

            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('数据上传失败，请重新导入')</script>");
                return null;
            }

        }
        private void AddSalaryDetailGridColumn(String itemIDs,String totalIDs)
        {
            DataTable dataTableItem = SqlHelper.ExecuteDataset(CommandType.Text, "SELECT REC_ID,SALARY_ITEM_NAME,SALARY_ITEM_TYPE,SALARY_ITEM_CODE FROM HOS_SALARY_ITEM WHERE REC_ID IN (" + itemIDs + ") ORDER BY SALARY_ITEM_SEQ").Tables[0];
            DataTable dataTableTotal = SqlHelper.ExecuteDataset(CommandType.Text, "SELECT REC_ID,TOTAL_ITEM_NAME,TOTAL_ITEM_TYPE,TOTAL_ITEM_CODE FROM HOS_SALARY_TOTAL WHERE REC_ID IN (" + totalIDs + ") ORDER BY TOTAL_ITEM_SEQ").Tables[0];
            DataRow[] dataRowsSalary = dataTableItem.Select("SALARY_ITEM_TYPE=1");
            DataRow[] dataRowsDeduct = dataTableItem.Select("SALARY_ITEM_TYPE=0");

            DataRow[] dataRowsTotalSalary = dataTableTotal.Select("TOTAL_ITEM_TYPE=1");
            DataRow[] dataRowsTotalDeduct = dataTableTotal.Select("TOTAL_ITEM_TYPE=0");
            foreach (DataRow item in dataRowsSalary)
            {

                RenderField renderField = new RenderField();
                renderField.HeaderText = item["SALARY_ITEM_NAME"].ToString();
                renderField.ColumnID= item["SALARY_ITEM_CODE"].ToString();
                renderField.DataField = item["SALARY_ITEM_CODE"].ToString();
                renderField.Width = 150;
                GridSalaryDetail.Columns.Add(renderField);
            }
            foreach (DataRow item in dataRowsTotalSalary)
            {

                RenderField renderField = new RenderField();
                renderField.HeaderText = item["TOTAL_ITEM_NAME"].ToString();
                renderField.ColumnID = item["TOTAL_ITEM_CODE"].ToString();
                renderField.DataField = item["TOTAL_ITEM_CODE"].ToString();
                renderField.Width = 150;
                GridSalaryDetail.Columns.Add(renderField);
            }
            foreach (DataRow item in dataRowsDeduct)
            {
                RenderField renderField = new RenderField();
                renderField.HeaderText = item["SALARY_ITEM_NAME"].ToString();
                renderField.ColumnID = item["SALARY_ITEM_CODE"].ToString();
                renderField.DataField = item["SALARY_ITEM_CODE"].ToString();
                renderField.Width = 150;
                GridSalaryDetail.Columns.Add(renderField);
            }
            foreach (DataRow item in dataRowsTotalDeduct)
            {

                RenderField renderField = new RenderField();
                renderField.HeaderText = item["TOTAL_ITEM_NAME"].ToString();
                renderField.ColumnID = item["TOTAL_ITEM_CODE"].ToString();
                renderField.DataField = item["TOTAL_ITEM_CODE"].ToString();
                renderField.Width = 150;
                GridSalaryDetail.Columns.Add(renderField);
            }
        }
        // 根据行ID来删除行数据
        private void DeleteRowByID(int rowID)
        {
            try
            {
                SqlHelper.ExecuteNonQuery(CommandType.Text, "DELETE FROM HOS_SALARY_TEMPLATE WHERE REC_ID=" + rowID);
                SqlHelper.ExecuteNonQuery(CommandType.Text, "DELETE FROM HOS_SALARY_DETAIL WHERE TEMPLATE_ID=" + rowID);
            }
            catch (Exception ex)
            {
                throw new Exception("删除失败！");
            }
        }
       
        protected void Grid1_RowCommand(object sender, GridCommandEventArgs e)
        {
            if (e.CommandName == "Delete")
            {
                int rowID = Convert.ToInt32(Grid1.DataKeys[e.RowIndex][0]);
                try
                {
                    DeleteRowByID(rowID);
                    BindData();
                    ShowNotify("删除数据成功!");

                }
                catch (Exception ex)
                {
                    ShowNotify("删除数据失败!");
                }
            }
        }
        protected void Grid1_PreRowDataBound(object sender, GridPreRowEventArgs e)
        {
            // 如果绑定到 DataTable，那么这里的 DataItem 就是 DataRowView
            DataRowView row = e.DataItem as DataRowView;
            int rowId = Convert.ToInt32(row["REC_ID"]);

            LinkButtonField editField = Grid1.FindColumn("Edit") as LinkButtonField;
            editField.OnClientClick = String.Format("showEditWindow('{0}');", rowId);
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            BindData();
        }
        protected void btnExport_Click(object sender, EventArgs e)
        {
            if (Grid1.SelectedRowIndexArray.Length > 0)
            {
                GridRow gridRow = Grid1.SelectedRow;
                String salaryName = gridRow.Values[1].ToString();
                String salaryMonth = gridRow.Values[2].ToString();
                String itemIDs = gridRow.Values[6].ToString();
                String totalIDs = gridRow.Values[7].ToString();
                AddSalaryDetailGridColumn(itemIDs, totalIDs);
                DataTable dataTable = SqlHelper.ExecuteDataset(CommandType.Text, "SELECT * FROM HOS_SALSRY_DETAIL WHERE TEMPLATE_ID=" + Grid1.SelectedRowID).Tables[0];
                GridSalaryDetail.DataSource = dataTable;
                GridSalaryDetail.DataBind();
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                book = PublicMethod.NPOIExcel(GridSalaryDetail);
                MemoryStream ms = new MemoryStream();
                book.Write(ms);
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(salaryMonth + "工资明细", System.Text.Encoding.UTF8)));
                Response.BinaryWrite(ms.ToArray());
                Response.End();
                book = null;
                ms.Close();
                ms.Dispose();
            }
            else
            {
                ShowNotify("未选中任何行!");
            }
        }
        protected void btnSendMessage_Click(object sender, EventArgs e)
        {
            if (Grid1.SelectedRowIndexArray.Length > 0)
            {
                FineUIPro.HiddenField hiFieldPageName = (FineUIPro.HiddenField)Toolbar2.FindControl("hiFieldPageName");
                FineUIPro.HiddenField hiFieldapprovalID = (FineUIPro.HiddenField)Toolbar2.FindControl("hiFieldapprovalID");
                String sql = @"SELECT A.*,(SELECT DEPT_NAME FROM DEPT_DICT WHERE REC_ID=A.PROCESS_DEPT) AS PROCESS_DEPT_NAME,
                               (SELECT USER_NAME FROM USER_DICT WHERE REC_ID=A.PROCESS_USER) AS PROCESS_USER_NAME
                               FROM HOS_APPROVAL_RECORDS AS A WHERE APPROVAL_ID={0} AND APPROVAL_DATA_ID={1} AND PROCESS_PAGE_NAME='{2}'";
                sql = string.Format(sql, hiFieldapprovalID.Text, Grid1.SelectedRowID, hiFieldPageName.Text);
                DataTable checkTable = SqlHelper.ExecuteDataset(CommandType.Text, sql).Tables[0];
                if (checkTable != null && checkTable.Rows.Count > 0)
                {
                    String showText = "";
                    bool isAllPass = true;
                    foreach (DataRow dataRow in checkTable.Rows)
                    {
                        if (dataRow["PROCESS_RESULT"].ToString() == "0")
                        {
                            isAllPass = false;
                            showText += dataRow["PROCESS_DEPT_NAME"].ToString() + "：" + dataRow["PROCESS_USER_NAME"].ToString() + "<br />";

                        }
                    }
                    if (isAllPass == false)
                    {
                        PublicMethod.AlertMessage("此信息未审批完结<br />待审信息：<br />" + showText, "审批提示");
                    }
                    else
                    {
                        GridRow gridRow = Grid1.SelectedRow;
                        String itemIDs = gridRow.Values[6].ToString();
                        DataTable itemTable = SqlHelper.ExecuteDataset(CommandType.Text, "SELECT SALARY_ITEM_NAME,SALARY_ITEM_CODE,SALARY_ITEM_TYPE FROM HOS_SALARY_ITEM").Tables[0];
                        DataTable totalTable = SqlHelper.ExecuteDataset(CommandType.Text, "SELECT TOTAL_ITEM_NAME,TOTAL_ITEM_CODE,TOTAL_ITEM_TYPE FROM HOS_SALARY_TOTAL").Tables[0];
                        DataTable salaryDetailTable = SqlHelper.ExecuteDataset(CommandType.Text, @"SELECT A.*,
                                                                                                    (SELECT DING_USER_ID FROM USER_DICT WHERE USER_CODE=A.USER_CODE) AS DING_CODE
                                                                                                    FROM HOS_SALSRY_DETAIL AS A WHERE A.TEMPLATE_ID=" + Grid1.SelectedRowID).Tables[0];
                        foreach (DataColumn dataColumn in salaryDetailTable.Columns)
                        {
                            DataRow[] itemCodeRow = itemTable.Select("SALARY_ITEM_CODE='" + dataColumn.ColumnName + "'");
                            DataRow[] totalCodeRow = totalTable.Select("SALARY_ITEM_CODE='" + dataColumn.ColumnName + "'");
                            if (itemCodeRow.Count() > 0)
                            {
                                if (itemCodeRow[0]["SALARY_ITEM_TYPE"].ToString() == "0")
                                {
                                    dataColumn.ColumnName = "K_" + itemCodeRow[0]["SALARY_ITEM_NAME"].ToString();
                                }
                                if (itemCodeRow[0]["SALARY_ITEM_TYPE"].ToString() == "1")
                                {
                                    dataColumn.ColumnName = "F_" + itemCodeRow[0]["SALARY_ITEM_NAME"].ToString();
                                } 
                            }
                            if (totalCodeRow.Count() > 0)
                            {
                                if (totalCodeRow[0]["TOTAL_ITEM_TYPE"].ToString() == "0")
                                {
                                    dataColumn.ColumnName = "TK_" + totalCodeRow[0]["TOTAL_ITEM_NAME"].ToString();
                                }
                                if (totalCodeRow[0]["TOTAL_ITEM_TYPE"].ToString() == "1")
                                {
                                    dataColumn.ColumnName = "TF_" + totalCodeRow[0]["TOTAL_ITEM_NAME"].ToString();
                                }
                            }
                        }
                        String messageShow = "";
                        for (int i = 0; i < salaryDetailTable.Rows.Count; i++)
                        {
                            StringBuilder strDeduct = new StringBuilder();
                            StringBuilder strSalary = new StringBuilder();
                            strDeduct.Append("扣款项：\n");
                            strSalary.Append("发款项：\n");
                            for (int j = 0; j < salaryDetailTable.Columns.Count; j++)
                            {
                                if (salaryDetailTable.Rows[i][j].ToString() != "")
                                {
                                    if (salaryDetailTable.Columns[j].ColumnName.Contains("K_"))
                                    {
                                        strDeduct.Append(salaryDetailTable.Columns[j].ColumnName.Replace("K_", "") + "：" + salaryDetailTable.Rows[i][j].ToString()+ ",\n");
                                    }
                                    if (salaryDetailTable.Columns[j].ColumnName.Contains("TK_"))
                                    {
                                        strDeduct.Append(salaryDetailTable.Columns[j].ColumnName.Replace("TK_", "") + "：" + salaryDetailTable.Rows[i][j].ToString() + ",\n");
                                    }
                                    if (salaryDetailTable.Columns[j].ColumnName.Contains("F_"))
                                    {
                                        strSalary.Append(salaryDetailTable.Columns[j].ColumnName.Replace("F_", "") + "：" + salaryDetailTable.Rows[i][j].ToString() + ",\n");
                                    }
                                    if (salaryDetailTable.Columns[j].ColumnName.Contains("TF_"))
                                    {
                                        strDeduct.Append(salaryDetailTable.Columns[j].ColumnName.Replace("TF_", "") + "：" + salaryDetailTable.Rows[i][j].ToString() + ",\n");
                                    }
                                }
                            }
                            
                           String Message= DingHelper.SendMessageToUser(gridRow.Values[2].ToString()+"工资明细\n"+strSalary.ToString().TrimEnd(',') + "\n" 
                                                                      + strSalary.ToString().TrimEnd(',')+"\n其他说明："+ gridRow.Values[4].ToString(), salaryDetailTable.Rows[i]["DING_CODE"].ToString());
                            JObject jObject = JObject.Parse(Message);
                            if (jObject["ErrCode"].ToString() != "0")
                            {
                                messageShow += salaryDetailTable.Rows[i]["USER_NAME"].ToString() + "：信息发送失败<br />";
                            }
                            
                        }
                        PublicMethod.AlertMessage(messageShow, "提示");

                    }
                }
                else
                {
                    PublicMethod.AlertMessage("当前数据未提交审批", "提示");
                }

            }
            else
            {
                ShowNotify("未选中任何行!");
            }
        }
        
        protected void btnDownTemplate_Click(object sender, EventArgs e)
        {

            if (Grid1.SelectedRowIndexArray.Length > 0)
            {
                GridRow gridRow = Grid1.SelectedRow;
                String itemIDs = gridRow.Values[6].ToString();
                String totalIDs = gridRow.Values[7].ToString();
                AddSalaryDetailGridColumn(itemIDs, totalIDs);
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                book= PublicMethod.NPOIExcel(GridSalaryDetail);
                MemoryStream ms = new MemoryStream();
                book.Write(ms);
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("工资明细模板", System.Text.Encoding.UTF8)));
                Response.BinaryWrite(ms.ToArray());
                Response.End();
                book = null;
                ms.Close();
                ms.Dispose();
            }
            else
            {
                ShowNotify("未选中任何行!");
            }
            
        }
        
        protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                Dictionary<String, String> keyValuePairs = new Dictionary<string, string>();
                keyValuePairs.Clear();
                if (tbxFormTEMPLATE_NAME.Text.Trim() != "")
                {
                    keyValuePairs.Add("TEMPLATE_NAME", tbxFormTEMPLATE_NAME.Text.Trim());
                }
                else
                {
                    ShowNotify("模板名不能为空!");
                    return;
                }
                if (tbxFormTEMPLATE_DATA.Text.Trim() != "")
                {
                    keyValuePairs.Add("TEMPLATE_DATA", tbxFormTEMPLATE_DATA.Text.Trim());
                }
                else
                {
                    ShowNotify("模板月份!");
                    return;
                }
                if (tbxFormTEMPLATE_REMARK.Text.Trim() != "")
                {
                    keyValuePairs.Add("TEMPLATE_REMARK", tbxFormTEMPLATE_REMARK.Text.Trim());
                }
                if (String.Join(",", chlFormSALARY_ITEMS.SelectedValueArray) != "" || String.Join(",", chlFormDEDUCT_ITEMS.SelectedValueArray) != "")
                {
                    String itemIDs = String.Join(",", chlFormSALARY_ITEMS.SelectedValueArray) +","+ String.Join(",", chlFormDEDUCT_ITEMS.SelectedValueArray);
                    keyValuePairs.Add("TEMPLATE_ITEM", itemIDs.TrimEnd(',').TrimStart(','));
                }
                else
                {
                    ShowNotify("工资条项目不能为空!");
                    return;
                }
                if (String.Join(",", chlFormTOTAL_SALARY_ITEMS.SelectedValueArray) != "" || String.Join(",", chlFormTOTAL_DEDUCT_ITEMS.SelectedValueArray) != "")
                {
                    String itemIDs = String.Join(",", chlFormTOTAL_SALARY_ITEMS.SelectedValueArray) + "," + String.Join(",", chlFormTOTAL_DEDUCT_ITEMS.SelectedValueArray);
                    keyValuePairs.Add("TEMPLATE_TOTAL", itemIDs.TrimEnd(',').TrimStart(','));
                }
                else
                {
                    ShowNotify("合计项目不能为空!");
                    return;
                }
                String strRowID = hfFormID.Text;
                if (String.IsNullOrEmpty(strRowID))
                {
                    keyValuePairs.Add("TEMPLATE_STATUS", "0");
                    keyValuePairs.Add("CREATE_TIME", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    keyValuePairs.Add("CREATE_USER", Session["UserID"].ToString());
                    PublicMethod.SetDataMethod("SELECT * FROM HOS_SALARY_TEMPLATE WHERE 1=2", "Add", keyValuePairs);

                    ShowNotify("保存成功!");
                }
                else
                {
                    keyValuePairs.Add("REC_ID", strRowID);
                    keyValuePairs.Add("UPDATE_TIME", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    keyValuePairs.Add("UPDATE_USER", Session["UserID"].ToString());
                    PublicMethod.SetDataMethod("SELECT * FROM HOS_SALARY_TEMPLATE WHERE REC_ID=" + strRowID, "Update", keyValuePairs);
                    ShowNotify("保存成功!");
                }
                // 重新绑定表格，并点击当前编辑或者新增的行
                BindData();
                PageContext.RegisterStartupScript(String.Format("F('{0}').selectRow('{1}');", Grid1.ClientID, "1") + Window1.GetHideReference());
            }
            catch (Exception ex)
            {

                throw new Exception("保存失败！");
            }

        }
        protected void btnLookDetail_Click(object sender, EventArgs e)
        {
            if (Grid1.SelectedRowIndexArray.Length > 0)
            {
                GridRow gridRow = Grid1.SelectedRow;
                String salaryStatus = gridRow.Values[5].ToString();
                if (salaryStatus == "0")
                {
                    ShowNotify("该工资条未上传明细!");
                    return;
                }
                else
                {
                    btnDownTemplate.Hidden = true;
                    btnExport.Hidden = false;
                    String itemIDs = gridRow.Values[6].ToString();
                    String totalIDs = gridRow.Values[7].ToString();
                    AddSalaryDetailGridColumn(itemIDs, totalIDs);
                    String rowID = Grid1.SelectedRowID;
                    DataTable dataTable = SqlHelper.ExecuteDataset(CommandType.Text, "SELECT * FROM HOS_SALSRY_DETAIL WHERE TEMPLATE_ID=" + rowID).Tables[0];
                    GridSalaryDetail.DataSource = dataTable;
                    GridSalaryDetail.DataBind();
                    PageContext.RegisterStartupScript(String.Format("F('{0}').show();", Window3.ClientID));
                }
            }
            else
            {
                ShowNotify("未选中任何行!");
            }
        }
        protected void btnSaveDetail_Click(object sender, EventArgs e)
        {
            string xlsName = GetExcel();
            if (xlsName != "")
            {
                String cmdText = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlsName + ";Extended Properties='Excel 12.0; HDR=YES;'";
                DataTable dt = null;
                //建立连接
                OleDbConnection conn = new OleDbConnection(cmdText);

                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();

                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                StringBuilder sqlInsert = new StringBuilder();
                StringBuilder sqlField = new StringBuilder();
                StringBuilder sqlValue = new StringBuilder();
                DataTable tableColumnItemCode = SqlHelper.ExecuteDataset(CommandType.Text, "SELECT SALARY_ITEM_NAME,SALARY_ITEM_CODE FROM HOS_SALARY_ITEM").Tables[0];
                DataTable tableColumnTotalCode = SqlHelper.ExecuteDataset(CommandType.Text, "SELECT TOTAL_ITEM_NAME,TOTAL_ITEM_CODE,TOTAL_ITEM_MAPPING FROM HOS_SALARY_TOTAL").Tables[0];
                if (dt.Rows.Count > 0)
                {
                    dt.Columns.Add("TEMPLATE_ID", typeof(int));
                    dt.Columns.Add("CREATE_TIME", typeof(DateTime));
                    dt.Columns.Add("CREATE_USER", typeof(int));
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        String excelColumnName = dt.Columns[i].ColumnName;
                        if (excelColumnName == "姓名")
                        {
                            dt.Columns[i].ColumnName = "USER_NAME";
                        }
                        else if (excelColumnName == "工号")
                        {
                            dt.Columns[i].ColumnName = "USER_CODE";
                        }
                        else
                        {
                            DataRow[] itemCodeRow = tableColumnItemCode.Select("SALARY_ITEM_NAME='" + excelColumnName + "'");
                            DataRow[] TotalCodeRow = tableColumnTotalCode.Select("TOTAL_ITEM_NAME='" + excelColumnName + "'");
                            if (itemCodeRow.Count() > 0)
                            {
                                dt.Columns[i].ColumnName = itemCodeRow[0]["SALARY_ITEM_CODE"].ToString();
                            }
                            if (TotalCodeRow.Count() > 0)
                            {
                                dt.Columns[i].ColumnName = TotalCodeRow[0]["TOTAL_ITEM_CODE"].ToString();
                            }
                        }
                    }
                    foreach (DataRow item in dt.Rows)
                    {
                        item["TEMPLATE_ID"] = Convert.ToInt32(Grid1.SelectedRowID);
                        item["CREATE_USER"] = Convert.ToInt32(Session["UserID"]);
                        item["CREATE_TIME"] = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    SqlHelper.ExecuteNonQuery(CommandType.Text, "DELETE FROM HOS_SALSRY_DETAIL WHERE TEMPLATE_ID=" + Grid1.SelectedRowID);
                    PublicMethod.InsertByDataTable(dt, "HOS_SALSRY_DETAIL");
                    PublicMethod.InsertByDataTable(dt, "HOS_SALSRY_DETAIL_BAK");
                    SqlHelper.ExecuteNonQuery(CommandType.Text, "UPDATE HOS_SALARY_TEMPLATE SET TEMPLATE_STATUS=1 WHERE REC_ID=" + Grid1.SelectedRowID);
                    BindData();
                    PageContext.RegisterStartupScript(Window2.GetHideReference());
                }
                else
                {
                    ShowNotify("读取Excel失败!");
                    return;
                }
                
            }
            else
            {
                ShowNotify("读取Excel失败!");
                return;
            }
        }
        protected void btnPreviewTemplate_Click(object sender, EventArgs e)
        {
            if (Grid1.SelectedRowIndexArray.Length > 0)
            {
                btnDownTemplate.Hidden = false;
                btnExport.Hidden = true;
                GridRow gridRow = Grid1.SelectedRow;
                String itemIDs = gridRow.Values[6].ToString();
                String totalIDs = gridRow.Values[7].ToString();
                AddSalaryDetailGridColumn(itemIDs, totalIDs);
                PageContext.RegisterStartupScript(String.Format("F('{0}').show();", Window3.ClientID));


            }
            else
            {
                ShowNotify("未选中任何行!");
            }
        }
    }
}